﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using MySQLDriverCS;
using SCQS.Models;
using System.Data.Common;

namespace SCQS.Handler
{
    public class DBManager
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connStr = @"Server=localhost;Database=scqs;Persist Security Info=True;User ID=root;Password=admin";

        /// <summary>
        /// 数据库连接对象，使用时建立
        /// </summary>
        private static MySQLConnection conn;
        private static string security_Code = "security_code";

        /// <summary>
        /// 查询防伪码
        /// </summary>
        /// <param name="code">防伪码字符串</param>
        /// <param name="ip">查询客户端ip地址字符串</param>
        /// <param name="dateTime">查询时间</param>
        /// <param name="node">用于接受查询信息</param>
        /// <returns></returns>
        public static int QueryCode(string code,string ip,string dateTime,QueryInfoNode node)
        {
            try
            {
                conn = new MySQLConnection(new MySQLConnectionString("scqs", "root", "admin").AsString);
                conn.Open();
                Debug.WriteLine("query code=" + code);
                int count = -1;//记录防伪码条数，存在时=1
                DbTransaction tran=conn.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    MySQLCommand selectSecurityCode = new MySQLCommand("select * from securityCode where digitals='" + code + "'", conn);
                    MySQLCommand selectQuerySecurityCode = new MySQLCommand("select * from querySecurityCode where digitals='" + code + "'", conn);
                    MySQLCommand update = new MySQLCommand("update securityCode set queryCount=queryCount+1 where digitals='" + code + "'", conn);

                    selectSecurityCode.Transaction = tran;
                    selectQuerySecurityCode.Transaction = tran;
                    update.Transaction = tran;

                    MySQLDataAdapter mdaCode = new MySQLDataAdapter(selectSecurityCode);
                    MySQLDataAdapter mdaQuery = new MySQLDataAdapter(selectQuerySecurityCode);
                    DataSet dsCode = new DataSet();
                    DataSet dsQuery = new DataSet();
                    mdaCode.Fill(dsCode, SecurityCodeNode.TableNameEng);
                    mdaQuery.Fill(dsQuery, QuerySecurityCodeNode.TableNameEng);
                    DataTable dtCode = dsCode.Tables[SecurityCodeNode.TableNameEng];

                    if (dtCode.Rows.Count == 1)
                    {
                        DataRow drCode = dtCode.Rows[0];

                        node.CodeID = drCode[SecurityCodeNode.IDNameEng].ToString();
                        node.Digitals = drCode[SecurityCodeNode.DigitalsNameEng].ToString();
                        node.Number = int.Parse(drCode[SecurityCodeNode.QueryCountNameEng].ToString());

                        MySQLCommand selectQueryHistory = new MySQLCommand("select * from querySecurityCode where digitals='" + code +
                            "' and queryNumber=" + drCode[SecurityCodeNode.QueryCountNameEng].ToString(), conn);
                        MySQLDataAdapter mdaHistory = new MySQLDataAdapter(selectQueryHistory);
                        DataSet dsHistory = new DataSet();
                        mdaHistory.Fill(dsHistory, QuerySecurityCodeNode.TableNameEng);
                        DataTable dtHistory = dsHistory.Tables[QuerySecurityCodeNode.TableNameEng];
                        if (dtHistory.Rows.Count > 0)
                        {
                            DataRow drHistory = dtHistory.Rows[0];
                            node.IP = drHistory[QuerySecurityCodeNode.QueryIPEng].ToString();
                            node.DateTime = drHistory[QuerySecurityCodeNode.QueryDateEng].ToString();
                        }
                        else
                        {
                            node.IP = "";
                            node.DateTime = "";
                        }

                        MySQLCommand insert = new MySQLCommand("insert querySecurityCode(codeID,digitals,queryNumber,queryIP,queryDate) values('" +
                        drCode[SecurityCodeNode.IDNameEng].ToString() + "','" + drCode[SecurityCodeNode.DigitalsNameEng].ToString() + "',1+" +
                        drCode[SecurityCodeNode.QueryCountNameEng] + ",'" +ip+"','"+ DateTime.Now.ToString() + "')", conn);
                        insert.Transaction = tran;
                        Debug.WriteLine(insert.CommandText);

                        count = 1;
                        Debug.WriteLine("count=" + count.ToString());
                        selectSecurityCode.ExecuteNonQuery();
                        selectQuerySecurityCode.ExecuteNonQuery();
                        selectQueryHistory.ExecuteNonQuery();
                        update.ExecuteNonQuery();
                        insert.ExecuteNonQuery();
                        tran.Commit();
                    }

                    Debug.WriteLine(dsCode.Tables.Count.ToString());
                    Debug.WriteLine(dsQuery.Tables.Count.ToString());

                    foreach (DataRow dr in dtCode.Rows)
                    {
                        int i, size = SecurityCodeNode.ColNameEng.Length;
                        for (i = 0; i < size; i++)
                        {
                            Debug.WriteLine(dr[SecurityCodeNode.ColNameEng[i]].ToString());
                        }
                    }
                    conn.Close();
                    return count;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    tran.Rollback();
                    conn.Close();
                    return -1;
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                conn.Close();
                return -1;
            }
        }

        public static DataTable ValidData(Boolean b)
        {
            MySQLCommand comd = new MySQLCommand();
            comd.Connection = conn;
            int valid = b ? 1 : 0;
            comd.CommandText = "select * from security_code where valid='" + valid + "';";

            MySQLDataAdapter da = new MySQLDataAdapter();
            da.SelectCommand = comd;
            DataSet ds = new DataSet();
            da.Fill(ds, security_Code);

            return ds.Tables[security_Code];
        }

        /// <summary>
        /// DbType转化为SqlDbType
        /// </summary>
        /// <param name="pSourceType"></param>
        /// <returns></returns>
        private static SqlDbType ConvertToSqlDbType(DbType pSourceType)
        {
            SqlParameter paraConver = new SqlParameter();
            paraConver.DbType = pSourceType;
            return paraConver.SqlDbType;
        }

        /// <summary>
        /// SqlDbType转化为DbType
        /// </summary>
        /// <param name="pSourceType"></param>
        /// <returns></returns>
        private static DbType ConverToDbType(SqlDbType pSourceType)
        {
            SqlParameter paraConver = new SqlParameter();
            paraConver.SqlDbType = pSourceType;
            return paraConver.DbType;
        }

        /// <summary>
        /// 将表table中的数据写入数据库，table中存放的是防伪码，。。。
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static int WirteNewCode(DataTable table)
        {
            if (table == null) return -1;

            try
            {
                conn = new MySQLConnection(new MySQLConnectionString("scqs", "root", "admin").AsString);
                conn.Open();
                MySQLCommand insert = new MySQLCommand();
                insert.CommandText = "insert into securityCode(codeID,digitals,startDate,expireDate,queryCount) values(" +
                    "@codeID,@digitals,@startDate,@expireDate,@queryCount);";
                insert.Connection = conn;

                insert.Parameters.Add("@codeID", DbType.String, "codeID");
                insert.Parameters.Add("@digitals", DbType.String, "digitals");
                insert.Parameters.Add("@startDate", DbType.DateTime, "startDate");
                insert.Parameters.Add("@expireDate", DbType.DateTime, "expireDate");
                insert.Parameters.Add("@queryCount", DbType.Int32, "queryCount");

                MySQLDataAdapter da = new MySQLDataAdapter();
                da.InsertCommand = insert;
                try
                {
                    int i = da.Update(table);
                    return i;
                }
                catch (Exception e)
                {
                    Debug.WriteLine(e.Message);
                    return -1;
                }
            }
            catch (Exception e)
            {
                Debug.WriteLine(e.Message);
                return -1;
            }
        }

        public static DataTable selectCode(string sql)
        {
            conn.Open();
            MySQLCommand select = new MySQLCommand(sql, conn);
            MySQLDataAdapter da = new MySQLDataAdapter(select);
            DataSet dataset = new DataSet();
            da.Fill(dataset, "securityCode");
            DataTable table = dataset.Tables["digitals"];

            conn.Close();
            return table;
        }

        public static int deleteCode(string sql)
        {
            MySQLCommand delete = new MySQLCommand(sql, conn);
            conn.Open();
            int count = Convert.ToInt16(delete.ExecuteNonQuery());
            conn.Close();
            return count;
        }
    }
}
